package com.wenda.homeschooedu.DB;


public interface DB {
	public interface Table {
		public static final String DATABASENAME = "homeschooledu";
		public static final int VERSION = 1;
		//public static final String IP_PORT = "192.168.1.107:1433";

		public interface Advise {
			public interface SQL {
				public static final String SELECT = "";
				public static final String INSERT = "";
				public static final String UPDATE = "";
				public static final String DELETE = "";
			}

		}

		public interface ClassDB {
			public interface SQL {
				public static final String SELECT = "select class._id as class_id , class.name as class_name , class.master_id , teacher.name as master_name , teacher.phone as master_phone , teacher.username as master_username , subject.name as master_major from (class inner join teacher on class.master_id = teacher._id ) inner join subject on teacher.subject_id = subject._id where %s ";
				public static final String INSERT = "";
				public static final String UPDATE = "";
				public static final String DELETE = "";
			}

		}

		public interface ClassToSubject {
			public interface SQL {
				public static final String SELECT = "";
				public static final String INSERT = "";
				public static final String UPDATE = "";
				public static final String DELETE = "";
			}

		}

		public interface Duty {
			public interface SQL {
				public static final String SELECT = " select duty.day,duty.student_id,duty.class_id,duty.week,student.name from duty inner join student on duty.student_id = student._id where duty.class_id=%s and duty.year = %s and duty.term = %s  and duty.week = %s ";
				public static final String INSERT = "";
				public static final String UPDATE = "";
				public static final String DELETE = "";
			}

		}

		public interface Exam {
			public interface SQL {
				public static final String SELECT = "";
				public static final String INSERT = "";
				public static final String UPDATE = "";
				public static final String DELETE = "";
			}

		}

		public interface Examplan {
			public interface SQL {
				public static final String SELECT = "select top(20) examplan._id,exam_id,thetime,description,exam.name as exam_name from examplan inner join exam on examplan.exam_id = exam._id where %s order by thetime desc";
				public static final String INSERT = "";
				public static final String UPDATE = "";
				public static final String DELETE = "";
			}

		}

		public interface Homework {
			public interface SQL {
				public static final String SELECT = "select homework._id ,homework.thetime, homework.class_id ,homework.subjectname,homework.thecontent,homework.subject_id,class.name as class_name  from homework inner join class on homework.class_id = class._id where %s";
				public static final String INSERT = "";
				public static final String UPDATE = "";
				public static final String DELETE = "";
			}

		}

		public interface Inform {
			public interface SQL {
				public static final String SELECT_BY_CLASS_ID = "select inform._id ,inform.thetime , inform.class_id , inform.title,inform.thecontent , inform.teacher_id , teacher.name as teacher_name from inform inner join teacher on inform.teacher_id = teacher._id where class_id = %s";
				public static final String SELECT_BY_OLD_ID = "select inform._id ,inform.thetime , inform.class_id , inform.title,inform.thecontent , inform.teacher_id , teacher.name as teacher_name from inform inner join teacher on inform.teacher_id = teacher._id where inform._id > %s and class_id = %s";
				public static final String INSERT = "";
				public static final String UPDATE = "";
				public static final String DELETE = "";
			}

		}

		public interface Lesson {
			public interface SQL {
				public static final String SELECT = " select  lesson._id ,lesson.class_id , lesson.subject_id , lesson.day , lesson.thetime , class.name as class_name , subject.name as subject_name  from (lesson inner join class on lesson.class_id = class._id ) inner join subject on lesson.subject_id = subject._id where lesson.class_id = %s and lesson.year = %s and lesson.term = %s order by thetime , day asc";
				public static final String INSERT = "";
				public static final String UPDATE = "";
				public static final String DELETE = "";
			}

		}
		
		public interface Parent{
			public interface SQL{
				public static final String SELECT= "select parent._id as parent_id , parent.name as parent_name,student_id,parent.username as parent_username,parent.phone as parent_phone,parent.address,student.name as student_name   from parent inner join student on parent.student_id = student._id where student.class_id = %s  ";
				public static final String SELECT_LOGIN = "	select * from parent where username = '%s' and password = '%s'";
				public static final String INSERT ="";
				public static final String UPDATE ="";
				public static final String DELETE = "";
			}
				
			
		}
		
		public interface Score{
			public interface SQL{
				public static final String SELECT= "select score2._id ,student_id ,student.name as student_name , score1 as chinese ,score2 as math ,score3 as english , score1+score2+score3 as total from score2 inner join student on score2.student_id = student._id where student.class_id = %s and year = %s and term =%s and exam =%s order by %s desc";
				public static final String INSERT ="";
				public static final String UPDATE ="";
				public static final String DELETE = "";
			}
				
			
		}
		public interface Student{
			public interface SQL{
				public static final String SELECT= "select _id , name , sex ,age,class_id ,username , phone ,address from student where class_id = %s";
				public static final String SELECT_CLASSID_BY_STUDENTID = "select class_id from student where _id  = %s";
				public static final String INSERT ="";
				public static final String UPDATE ="";
				public static final String DELETE = "";
			}
				
			
		}
		
		public interface Subject{
			public interface SQL{
				public static final String SELECT= "";
				public static final String INSERT ="";
				public static final String UPDATE ="";
				public static final String DELETE = "";
			}
				
			
		}
		
		
		public interface Teacher{
			public interface SQL{
				public static final String SELECT= "";
				public static final String INSERT ="";
				public static final String UPDATE ="";
				public static final String DELETE = "";
			}
				
			
		}
		
		
		public interface BasicInformation{
			public interface SQL{
				public static final String SELECT= "select  parent.name as parent_name , parent._id as parent_id , parent.username as parent_username , student._id as student_id ,student.name as student_name, class._id as class_id , class.name as class_name , teacher._id as teacher_id , teacher.name as teacher_name    from ((parent inner join student on parent.student_id = student._id) inner join class on student.class_id = class._id) inner join teacher on  class.master_id = teacher._id where parent.username = '%s' and parent.password = '%s' ";
				public static final String INSERT ="";
				public static final String UPDATE ="";
				public static final String DELETE = "";
			}
				
			
		}

	}
}
